import pymysql
from pymysql.cursors import DictCursor
from typing import Dict, Optional, List, Tuple
import pymysql
from src.logger import logger
from src.data.storage import password_storage

class DatabaseManager:
    """数据库管理核心业务逻辑类"""
    
    def __init__(self, storage_path: str = "~/.mysqlpass/passwords.json"):
        """初始化数据库管理器"""
        # 设置存储路径
        password_storage.set_storage_path(storage_path)
        # 当前活动连接
        self.active_connection = None
    
    def add_connection(self, name: str, host: str, port: str, username: str, password: str) -> bool:
        """添加或更新MySQL连接配置"""
        success = password_storage.add_connection(name, host, port, username, password)
        if success:
            logger.info(f"成功添加连接配置: {name}")
        return success
    
    def get_connection(self, name: str) -> Optional[Dict[str, str]]:
        """获取MySQL连接配置"""
        return password_storage.get_connection(name)
    
    def list_connections(self) -> list:
        """列出所有保存的连接名称"""
        return password_storage.list_connections()
    
    def delete_connection(self, name: str) -> bool:
        """删除MySQL连接配置"""
        success = password_storage.delete_connection(name)
        if success:
            logger.info(f"成功删除连接配置: {name}")
        return success
    
    def test_connection(self, name: str) -> Tuple[bool, str]:
        """测试MySQL连接是否可用"""
        conn_info = self.get_connection(name)
        if not conn_info:
            return False, "连接配置不存在"
        
        try:
            # 尝试连接数据库
            conn = pymysql.connect(
                host=conn_info['host'],
                port=int(conn_info['port']),
                user=conn_info['username'],
                password=conn_info['password'],
                connect_timeout=5
            )
            conn.close()
            return True, "连接成功"
        except Exception as e:
            return False, f"连接失败: {str(e)}"
    
    def get_databases(self, name: str) -> Tuple[bool, List[str] or str]:
        """获取MySQL连接下的所有数据库"""
        conn_info = self.get_connection(name)
        if not conn_info:
            return False, "连接配置不存在"
        
        try:
            # 连接数据库
            conn = pymysql.connect(
                host=conn_info['host'],
                port=int(conn_info['port']),
                user=conn_info['username'],
                password=conn_info['password'],
                connect_timeout=5
            )
            
            cursor = conn.cursor()
            # 查询所有数据库
            cursor.execute("SHOW DATABASES")
            databases = [db[0] for db in cursor.fetchall()]
            
            cursor.close()
            conn.close()
            
            return True, databases
        except Exception as e:
            return False, f"获取数据库列表失败: {str(e)}"
    
    def get_tables(self, name: str, database: str) -> Tuple[bool, List[str] or str]:
        """获取指定数据库中的所有表"""
        logger.info(f"开始获取数据库 {database} 的表列表 (连接: {name})")
        conn_info = self.get_connection(name)
        if not conn_info:
            logger.error(f"获取表列表失败: 连接配置 {name} 不存在")
            return False, "连接配置不存在"
        
        try:
            # 连接数据库
            conn = pymysql.connect(
                host=conn_info['host'],
                port=int(conn_info['port']),
                user=conn_info['username'],
                password=conn_info['password'],
                database=database,
                connect_timeout=5
            )
            
            cursor = conn.cursor()
            # 查询所有表
            cursor.execute("SHOW TABLES")
            tables = [table[0] for table in cursor.fetchall()]
            
            cursor.close()
            conn.close()
            
            logger.info(f"成功获取数据库 {database} 的表列表，共 {len(tables)} 张表")
            return True, tables
        except Exception as e:
            logger.error(f"获取表列表失败: {str(e)}")
            return False, f"获取表列表失败: {str(e)}"
    
    def execute_query(self, name: str, database: str, query: str) -> Tuple[bool, List[Dict] or str, List[str] or None]:
        """执行SQL查询"""
        conn_info = self.get_connection(name)
        if not conn_info:
            return False, "连接配置不存在", None
        
        try:
            # 连接数据库
            conn = pymysql.connect(
                host=conn_info['host'],
                port=int(conn_info['port']),
                user=conn_info['username'],
                password=conn_info['password'],
                database=database,
                connect_timeout=5,
                cursorclass=DictCursor
            )
            
            cursor = conn.cursor()
            
            # 执行查询
            cursor.execute(query)
            
            # 获取列名
            columns = [desc[0] for desc in cursor.description] if cursor.description else []
            
            # 获取结果
            results = cursor.fetchall() if cursor.description else []
            
            # 对于非查询语句，需要提交事务
            if not cursor.description:
                conn.commit()
                results = [{"affected_rows": cursor.rowcount}]
            
            cursor.close()
            conn.close()
            
            logger.info(f"查询执行成功，结果行数: {len(results) if results else 0}")
            return True, results, columns
        except Exception as e:
            logger.error(f"执行查询失败: {str(e)}")
            return False, f"执行查询失败: {str(e)}", None

    def get_table_structure(self, name: str, database: str, table: str) -> Tuple[bool, List[Dict] or str]:
        """获取指定表的结构信息"""
        logger.info(f"开始获取表 {table} 的结构信息 (数据库: {database}, 连接: {name})")
        conn_info = self.get_connection(name)
        if not conn_info:
            logger.error(f"获取表结构失败: 连接配置 {name} 不存在")
            return False, "连接配置不存在"
        
        try:
            # 连接数据库
            conn = pymysql.connect(
                host=conn_info['host'],
                port=int(conn_info['port']),
                user=conn_info['username'],
                password=conn_info['password'],
                database=database,
                connect_timeout=5,
                cursorclass=DictCursor
            )
            
            cursor = conn.cursor()
            # 查询表结构
            cursor.execute(f"DESCRIBE {table}")
            structure = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            logger.info(f"成功获取表 {table} 的结构信息，共 {len(structure)} 个字段")
            return True, structure
        except Exception as e:
            logger.error(f"获取表结构失败: {str(e)}")
            return False, f"获取表结构失败: {str(e)}"
            
    def get_table_create_sql(self, name: str, database: str, table: str) -> Tuple[bool, str]:
        """获取指定表的建表SQL语句"""
        logger.info(f"开始获取表 {table} 的建表SQL语句 (数据库: {database}, 连接: {name})")
        conn_info = self.get_connection(name)
        if not conn_info:
            logger.error(f"获取建表SQL失败: 连接配置 {name} 不存在")
            return False, "连接配置不存在"
        
        try:
            # 连接数据库
            conn = pymysql.connect(
                host=conn_info['host'],
                port=int(conn_info['port']),
                user=conn_info['username'],
                password=conn_info['password'],
                database=database,
                connect_timeout=5
            )
            
            cursor = conn.cursor()
            # 查询建表SQL
            cursor.execute(f"SHOW CREATE TABLE {table}")
            result = cursor.fetchone()
            
            cursor.close()
            conn.close()
            
            if result and len(result) >= 2:
                create_sql = result[1]
                logger.info(f"成功获取表 {table} 的建表SQL语句")
                return True, create_sql
            else:
                logger.error(f"获取建表SQL失败: 查询结果为空")
                return False, "获取建表SQL失败: 查询结果为空"
        except Exception as e:
            logger.error(f"获取建表SQL失败: {str(e)}")
            return False, f"获取建表SQL失败: {str(e)}"

# 创建一个默认的数据库管理器实例
db_manager = DatabaseManager()